<html>
<head>
<title>Создание базы данных</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
</head>
<body>
<?php


$link = mysql_connect('localhost', 'root', 'the1soop');
if (!$link) {
    die('Ошибка соединения: ' . mysql_error());
}
echo 'Успешно соединились';

$database = 'lectures';
if (!mysql_select_db($database)) {
    echo "<h1>Can't select database</h1>";
    // die("");
}


$result = mysql_query("CREATE TABLE `user` (
                         `user_id` int(10) unsigned NOT NULL auto_increment,
                         `login` varchar(256) default NULL,
                         `password` varchar(30) default NULL,
                         `email` varchar(256) default NULL,
                         `rating` int(10) unsigned NOT NULL DEFAULT 0,
                         `first_name` varchar(30),
                         `second_name` varchar(30),
                         `profile_picture_url` varchar(256),
                         `is_moderator` bool,
                         `points` int(10) unsigned NOT NULL DEFAULT 0,
                         `vip_points` int(10) unsigned NOT NULL DEFAULT 0,
                         PRIMARY KEY  (`user_id`),
                         UNIQUE (`login`)
                       )");
if (!$result) {
     echo "<br/>Table user was not created";
}


$result = mysql_query("CREATE TABLE `attachment_type` (
                         `attachment_type_id` int(10) unsigned NOT NULL auto_increment,
                         `name` varchar(256),
                         PRIMARY KEY  (`attachment_type_id`),
                         UNIQUE (`name`)
                       )");
if (!$result) {
     echo "<br/>Table attachment_type was not created";
}


$result = mysql_query("CREATE TABLE `reading_place` (
                         `reading_place_id` int(10) unsigned NOT NULL auto_increment,
                         `name` varchar(256) default NULL,
                         `description` varchar(4096) default NULL,
                         `home_page_url` varchar(256) default NULL,
                         `address` varchar(256) default NULL,
                         `logo_url` varchar(256) default NULL,
                         PRIMARY KEY  (`reading_place_id`),
                         UNIQUE (`name`)
                       )");
if (!$result) {
     echo "<br/>Table reading_place was not created";
}


$result = mysql_query("CREATE TABLE `subject` (
                         `subject_id` int(10) unsigned NOT NULL auto_increment,
                         `name` varchar(256) default NULL,
                         `description` varchar(4096) default NULL,
                         PRIMARY KEY  (`subject_id`),
                         UNIQUE (`name`)
                       )");
if (!$result) {
     echo "<br/>Table subject was not created";
}


$result = mysql_query("CREATE TABLE `course` (
                         `course_id` int(10) unsigned NOT NULL auto_increment,
                         `user_id` int(10) unsigned,
                         `subject_id` int(10) unsigned,
                         `reading_place_id` int(10) unsigned,
                         `title` varchar(256) default NULL,
                         `description` varchar(4096) default NULL,
                         `date_from` date,
                         `date_to` date,
                         `author` varchar(256),
                         `course_home_page` varchar(256),
                         `is_public` bool NOT NULL DEFAULT true,
                         `was_moderated` bool,
                          PRIMARY KEY  (`course_id`),
                          FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
                          FOREIGN KEY (`subject_id`) REFERENCES `subject` (`subject_id`),
                          FOREIGN KEY (`reading_place_id`) REFERENCES `reading_place` (`reading_place_id`),
                          UNIQUE (`title`)
                       )");
if (!$result) {
     echo "<br/>Table course was not created";
}


$result = mysql_query("CREATE TABLE `lecture` (
                         `lecture_id` int(10) unsigned NOT NULL auto_increment,
                         `course_id` int(10) unsigned,
                         `title` varchar(256),
                         `description` varchar(256),
                         `reading_date` date,
                         `publish_date` date,
                         `was_moderated` bool,
                          PRIMARY KEY  (`lecture_id`),
                          FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`),
                          UNIQUE (`title`)
                       )");
if (!$result) {
     echo "<br/>Table lecture was not created";
}


$result = mysql_query("CREATE TABLE `attachment` (
                         `attachment_id` int(10) unsigned NOT NULL auto_increment,
                         `attachment_type_id` int(10) unsigned,
                         `name` varchar(256),
                         `description` varchar(4096),
                         `url` varchar(256),
                         `was_moderated` bool,
                         `user_id` int(10) unsigned,
                         `lecture_id` int(10) unsigned,
                          PRIMARY KEY  (`attachment_id`),
                          FOREIGN KEY (`attachment_type_id`) REFERENCES `attachment_type` (`attachment_type_id`),
                          FOREIGN KEY (`lecture_id`) REFERENCES `lecture` (`lecture_id`),
                          FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
                          UNIQUE (`name`)
                       )");
if (!$result) {
     echo "<br/>Table attachment was not created";
}

$result = mysql_query("CREATE TABLE `access_to_private_course` (
                        `access_to_private_course_id` int(10) unsigned NOT NULL auto_increment,
                        `course_id` int(10) unsigned,
                        `user_id` int(10) unsigned,
                        PRIMARY KEY (`access_to_private_course_id`),
                        FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`),
                        FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
                      )");

if (!$result) {
    echo "<br/>Table access_to_private_course was not created";
}

$result = mysql_query("CREATE TABLE `order` (
                        `order_id` int(10) unsigned NOT NULL auto_increment,
                        `user_id` int(10) unsigned,
                        `attachment_id` int(10) unsigned,
                        `sending_date` date,
                        `deadline` date,
                        `status` int(1),
                        PRIMARY KEY (`order_id`),
                        FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
                        FOREIGN KEY (`attachment_id`) REFERENCES `attachment` (`attachment_id`)
                     )");

if (!$result) {
    echo "<br/> Table Order was not created";
}


$result = mysql_query("CREATE TABLE `internal_transaction` (
                         `internal_transaction_id` int(10) unsigned NOT NULL auto_increment,
                         `user_id_sender` int(10) unsigned,
                         `user_id_receiver` int(10) unsigned,
                         `open_access_action_id` int(10) unsigned,
                         `order_action_id` int(10) unsigned,
                         `points` int(10) unsigned NOT NULL DEFAULT 0,
                         `vip_points` int(10) unsigned NOT NULL DEFAULT 0,
                         PRIMARY KEY (`internal_transaction_id`),
                         FOREIGN KEY (`user_id_sender`) REFERENCES `user` (`user_id`),
                         FOREIGN KEY (`user_id_receiver`) REFERENCES `user` (`user_id`),
                         FOREIGN KEY (`open_access_action_id`) REFERENCES `access_to_private_course` (`access_to_private_course_id`),
                         FOREIGN KEY (`order_action_id`) REFERENCES `order` (`order_id`)
                       )");

if (!$result) {
    echo "<br/>Table InternalTransaction was not created";
}

$result = mysql_query("CREATE TABLE `input_transaction`(
                        `input_transaction_id` int(10) unsigned NOT NULL auto_increment,
                        `user_id` int(10) unsigned,
                        `total_sum` int(10) unsigned,
                        `current_date` date,
                        PRIMARY KEY (`input_transaction_id`),
                        FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
                      )");

if (!$result) {
    echo "<br/>Table InputTransaction was not created";
}


$result = musql_query("CREATE TABLE `output_transaction`(
                        `output_transaction_id` int(10) unsigned NOT NULL auto_increment,
                        `user_id` int(10) unsigned,
                        `current_date` date,
                        `total_sum` int(10) unsigned,
                        `commission` int(10) unsigned,
                        PRIMARY KEY (`output_transaction_id`),
                        FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
                       )");


if (!$result) {
    echo "<br/>Table OutputTransaction was not created";
}



mysql_close($link);
?>
</body>
</html>
